import QtQuick 2.0
import QtQuick.LocalStorage  2.0 as Sql

Item {


    function getDatabase() {
         return  Sql.LocalStorage.openDatabaseSync("MyAppName5", "1.0", "StorageDatabase", 100000);
    }

    // At the start of the application, we can initialize the tables we need if they haven't been created yet
    function initialize() {
        var db = getDatabase();
        db.transaction(
            function(tx) {
                // Create the settings table if it doesn't already exist
                // If the table exists, this is skipped
                tx.executeSql('CREATE TABLE IF NOT EXISTS login(user TEXT UNIQUE, pass TEXT,lv INTEGER)');
                tx.executeSql('CREATE TABLE IF NOT EXISTS topic(topicid INTEGER  PRIMARY KEY AUTOINCREMENT, name_topic TEXT,date_create TEXT)');
                tx.executeSql('CREATE TABLE IF NOT EXISTS fish(fishid INTEGER  PRIMARY KEY AUTOINCREMENT,rfid TEXT,num INTEGER)');
                tx.executeSql('CREATE TABLE IF NOT EXISTS mes( mesid INTEGER  PRIMARY KEY AUTOINCREMENT, image_filename TEXT, kg REAL ,size_h REAL ,size_w REAL, date_mes TEXT,topicid INTEGER, fishid INTEGER,  FOREIGN KEY (topicid) REFERENCES topic(topicid), FOREIGN KEY (fishid) REFERENCES fish(fishid))');
          });
    }

    // This function is used to write a setting into the database
    function setTopic(name_topic, date_create) {
       // setting: string representing the setting name (eg: “username”)
       // value: string representing the value of the setting (eg: “myUsername”)
       var db = getDatabase();
       var res = "";
       db.transaction(function(tx) {
           var rs = tx.executeSql('INSERT OR REPLACE INTO topic(name_topic,date_create) VALUES (?,?);', [name_topic,date_create]);
          // var rs = tx.executeSql('INSERT INTO topic VALUES (?,?);', [name_topic,date]);

                  //console.log(rs.rowsAffected)
                  if (rs.rowsAffected > 0) {
                    res = "OK";
                  } else {
                    res = "Error";
                  }
            }
      );
      // The function returns “OK” if it was successful, or “Error” if it wasn't
      return res;
    }
    // This function is used to retrieve a setting from the database
    function getTopic(topicid) {
       var db = getDatabase();
       var res="";
       db.transaction(function(tx) {
         var rs = tx.executeSql('SELECT * FROM topic WHERE topicid=?;', [topicid]);
         if (rs.rows.length > 0) {
              res = rs.rows.item(0).name_topic;
         } else {
             res = "Unknown";
         }
      })
      // The function returns “Unknown” if the setting was not found in the database
      // For more advanced projects, this should probably be handled through error codes
      return res
    }


    function loadRecordListTopic()
    {
         var db = getDatabase();
        var arrOut = new Array();

        db.transaction(
            function(tx) {
                var strSql = "SELECT * FROM topic";
                var rs = tx.executeSql(strSql);
                console.log(strSql)

                for(var i = 0; i < rs.rows.length; i++) {
                    arrOut.push(rs.rows.item(i))
                }

            }
        )
        return arrOut;
    }
    function makeRecordListTopic(id)
    {
        var db = getDatabase();
        db.transaction(
            function(tx) {
                var strSql = "SELECT * FROM topic";
                var rs = tx.executeSql(strSql);
                console.log(strSql)
                 id.clear();
                for(var i = 0; i < rs.rows.length; i++) {
                    var reg = rs.rows.item(i)
                    id.append({order:reg.topicid.toString(), Type: reg.name_topic, ballColor: reg.date_create});

                }

            }
        )


    }


//////////////////////////////fish///////////////////////////////////////////

    // This function is used to write a setting into the database
    function setFish(rfid,num) {
       // setting: string representing the setting name (eg: “username”)
       // value: string representing the value of the setting (eg: “myUsername”)
       var db = getDatabase();
       var res = "";
       db.transaction(function(tx) {
           var rs = tx.executeSql('INSERT OR REPLACE INTO fish(rfid,num) VALUES(?,?);', [rfid,num]);
                  //console.log(rs.rowsAffected)
                  if (rs.rowsAffected > 0) {
                    res = "OK";
                  } else {
                    res = "Error";
                  }
            }
      );
      // The function returns “OK” if it was successful, or “Error” if it wasn't
      return res;
    }
    // This function is used to retrieve a setting from the database
    function getFish(rfid) {
       var db = getDatabase();
       var res="";
       db.transaction(function(tx) {
         var rs = tx.executeSql('SELECT fishid FROM fish WHERE rfid=?;', [rfid]);
         if (rs.rows.length > 0) {
              res = rs.rows.item(0).fishid;
         } else {
             res = "Unknown";
         }
      })
      // The function returns “Unknown” if the setting was not found in the database
      // For more advanced projects, this should probably be handled through error codes
      return res
    }

////////////////////////////////login/////////////////////////////////////////

    // This function is used to write a setting into the database
    function setLogin(user,pass,lv) {
       // setting: string representing the setting name (eg: “username”)
       // value: string representing the value of the setting (eg: “myUsername”)
       var db = getDatabase();
       var res = "";
       db.transaction(function(tx) {
            var rs = tx.executeSql('INSERT OR REPLACE INTO login VALUES (?,?,?);', [user,pass,lv]);
                  //console.log(rs.rowsAffected)
                  if (rs.rowsAffected > 0) {
                    res = "OK";
                  } else {
                    res = "Error";
                  }
            }
      );
      // The function returns “OK” if it was successful, or “Error” if it wasn't
      return res;
    }
    // This function is used to retrieve a setting from the database
    function getLogin(pass) {
       var db = getDatabase();
       var res="";
       db.transaction(function(tx) {
         var rs = tx.executeSql('SELECT user FROM login WHERE pass=?;', [pass]);
         if (rs.rows.length > 0) {
              res = rs.rows.item(0).user;
         } else {
             res = "Unknown";
         }
      })
      // The function returns “Unknown” if the setting was not found in the database
      // For more advanced projects, this should probably be handled through error codes
      return res
    }
}
